package al.jdbc.DAO;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import al.jdbc.dpbridge.DataSource;
import al.jdbc.dpbridge.MySqlDataSource;
import alOmrane.jdbc.beans.Courrier_arrive;
import alOmrane.jdbc.beans.Piece_jointe_arrive;

public class Piece_jointe_arriveDAO {
	
	private Connection db;
	@SuppressWarnings("unused")
	private DataSource ds;
	private Courrier_arriveDAO cdao = new Courrier_arriveDAO(new MySqlDataSource("jdbc:mysql://localhost/gestion_courriers", "root"));
	private Statement sql;
	public Piece_jointe_arriveDAO(DataSource ds){
		db = ds.getConnection();
		this.ds = ds;
		try {
			sql = db.createStatement();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public int pjaCount(Courrier_arrive c){
		try {
			String query = "SELECT COUNT(*) FROM piece_jointe_arrive WHERE id_courrier_arrive="+c.getId()+" ;";

			ResultSet rs = sql.executeQuery(query);
			rs.next();
			return Integer.parseInt(rs.getString(1));
		}catch(Exception ex){
			return 0;
		}
	}
	
	public List<Piece_jointe_arrive> findByCourrier(Courrier_arrive c){
		List<Piece_jointe_arrive> pjs = new ArrayList<Piece_jointe_arrive>();
		try {
			String query = "SELECT * FROM piece_jointe_arrive WHERE id_courrier_arrive="+c.getId()+" ;";

			ResultSet rs = sql.executeQuery(query);
			while(rs.next()){
				Piece_jointe_arrive pj = new Piece_jointe_arrive(Integer.parseInt(rs.getString(1)),
						Integer.parseInt(rs.getString(2)),
						rs.getString(3), 
						cdao.findById(Integer.parseInt(rs.getString(4))));
				pjs.add(pj);
			}
			return pjs;
		}catch(Exception ex){
			return null;
		}
	}
	
	public boolean insert(Piece_jointe_arrive p){
		try{

			StringBuffer sb = new StringBuffer("INSERT INTO `piece_jointe_arrive` ( `num_piece_jointe_arrive`, `obj_piece_jointe_arrive`, `id_courrier_arrive`) VALUES " +
					"('"+p.getNum_piece_jointe_arrive()+"', '"+
					p.getObj_piece_jointe_arrive()+"', '"+
					p.getCourrier_arrive().getId()+"');");
			sql.executeUpdate(sb.toString());
			return true;
		}catch(Exception ex){
			ex.printStackTrace();
			return false;
		}
	}
	public boolean delete(int idCourrier){
		try{

			StringBuffer sb = new StringBuffer("DELETE FROM piece_jointe_arrive "+
					"WHERE id_courrier_arrive="+idCourrier+" ;");
			sql.executeUpdate(sb.toString());
			return true;
		}catch(Exception ex){
			System.out.println(ex.getMessage());
			return false;
		}
	}
}
